package com.pokermind.dse.util;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

/**
 * 数据库Schema更新工具
 * 用于为现有MySQL数据库添加缺失的字段
 */
@Component
public class DatabaseSchemaUpdater {
    
    private final JdbcTemplate jdbcTemplate;
    private static final String DATABASE_NAME = "soulwork";
    
    public DatabaseSchemaUpdater(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    
    /**
     * 检查并更新poker_hands表的缺失字段
     */
    public void updatePokerHandsTable() {
        System.out.println("🔧 开始检查并更新poker_hands表结构...");
        
        // 检查并添加current_street字段
        addColumnIfNotExists("poker_hands", "current_street", 
            "VARCHAR(10) DEFAULT 'preflop' COMMENT '当前街道(preflop/flop/turn/river)'");
        
        // 检查并添加ante字段
        addColumnIfNotExists("poker_hands", "ante", 
            "DECIMAL(10,2) DEFAULT 0 COMMENT '前注金额'");
            
        // 检查并添加其他可能缺失的字段
        addColumnIfNotExists("poker_hands", "board_cards", 
            "TEXT COMMENT '公共牌 JSON格式'");
            
        addColumnIfNotExists("poker_hands", "to_call", 
            "DECIMAL(10,2) DEFAULT 0 COMMENT '需要跟注的金额'");
            
        addColumnIfNotExists("poker_hands", "min_raise", 
            "DECIMAL(10,2) DEFAULT 0 COMMENT '最小加注金额'");
            
        addColumnIfNotExists("poker_hands", "status", 
            "VARCHAR(20) DEFAULT 'active' COMMENT '手牌状态'");
            
        addColumnIfNotExists("poker_hands", "hero_net_result", 
            "DECIMAL(10,2) DEFAULT 0 COMMENT 'Hero净收益'");
            
        addColumnIfNotExists("poker_hands", "hero_final_action", 
            "VARCHAR(20) COMMENT 'Hero最终行动'");
            
        addColumnIfNotExists("poker_hands", "hero_went_to_showdown", 
            "BOOLEAN DEFAULT false COMMENT '是否到摊牌'");
            
        addColumnIfNotExists("poker_hands", "final_pot", 
            "DECIMAL(10,2) DEFAULT 0 COMMENT '最终底池'");
            
        addColumnIfNotExists("poker_hands", "rake_amount", 
            "DECIMAL(10,2) DEFAULT 0 COMMENT '抽水金额'");
            
        addColumnIfNotExists("poker_hands", "players_to_flop", 
            "INTEGER DEFAULT 0 COMMENT '看到翻牌的玩家数'");
            
        addColumnIfNotExists("poker_hands", "players_to_turn", 
            "INTEGER DEFAULT 0 COMMENT '看到转牌的玩家数'");
            
        addColumnIfNotExists("poker_hands", "players_to_river", 
            "INTEGER DEFAULT 0 COMMENT '看到河牌的玩家数'");
            
        addColumnIfNotExists("poker_hands", "players_to_showdown", 
            "INTEGER DEFAULT 0 COMMENT '到摊牌的玩家数'");
            
        addColumnIfNotExists("poker_hands", "total_actions", 
            "INTEGER DEFAULT 0 COMMENT '总行动次数'");
            
        addColumnIfNotExists("poker_hands", "players_info", 
            "TEXT COMMENT '玩家信息JSON'");
            
        addColumnIfNotExists("poker_hands", "final_results", 
            "TEXT COMMENT '最终结果JSON'");
            
        addColumnIfNotExists("poker_hands", "started_at", 
            "TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '开始时间'");
            
        addColumnIfNotExists("poker_hands", "completed_at", 
            "TIMESTAMP NULL COMMENT '结束时间'");
            
        addColumnIfNotExists("poker_hands", "duration_seconds", 
            "INTEGER DEFAULT 0 COMMENT '持续时间(秒)'");
        
        System.out.println("✅ poker_hands表结构更新完成！");
    }
    
    /**
     * 检查字段是否存在，如果不存在则添加
     */
    private void addColumnIfNotExists(String tableName, String columnName, String columnDefinition) {
        try {
            // 检查列是否存在
            String checkSql = """
                SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND COLUMN_NAME = ?
                """;
                
            int count = jdbcTemplate.queryForObject(checkSql, Integer.class, 
                DATABASE_NAME, tableName, columnName);
            
            if (count == 0) {
                // 字段不存在，添加字段
                String alterSql = String.format("ALTER TABLE %s ADD COLUMN %s %s", 
                    tableName, columnName, columnDefinition);
                    
                System.out.println("  ➕ 添加字段: " + columnName);
                jdbcTemplate.execute(alterSql);
                System.out.println("     ✅ 字段 " + columnName + " 添加成功");
            } else {
                System.out.println("  ✓ 字段 " + columnName + " 已存在");
            }
            
        } catch (Exception e) {
            System.err.println("  ❌ 处理字段 " + columnName + " 时出错: " + e.getMessage());
            // 继续处理其他字段，不中断整个过程
        }
    }
    
    /**
     * 显示当前表结构
     */
    public void showTableStructure(String tableName) {
        System.out.println("\n📋 当前 " + tableName + " 表结构:");
        
        String sql = """
            SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? 
            ORDER BY ORDINAL_POSITION
            """;
            
        try {
            List<Map<String, Object>> columns = jdbcTemplate.queryForList(sql, DATABASE_NAME, tableName);
            
            System.out.printf("%-25s %-15s %-10s %-15s %s%n", 
                "COLUMN_NAME", "DATA_TYPE", "NULLABLE", "DEFAULT", "COMMENT");
            System.out.println("-".repeat(100));
            
            for (Map<String, Object> column : columns) {
                System.out.printf("%-25s %-15s %-10s %-15s %s%n",
                    column.get("COLUMN_NAME"),
                    column.get("DATA_TYPE"),
                    column.get("IS_NULLABLE"),
                    column.get("COLUMN_DEFAULT"),
                    column.get("COLUMN_COMMENT"));
            }
            
        } catch (Exception e) {
            System.err.println("❌ 获取表结构失败: " + e.getMessage());
        }
    }
}
